package ict.kth.se.model.repository.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import ict.kth.se.exceptions.EntityAlreadyExistsInDBException;
import ict.kth.se.exceptions.EntityDoesntExistInDBException;
import ict.kth.se.model.CompetenceProfile;
import ict.kth.se.model.repository.CompetenceProfileRepository;
import ict.kth.se.model.repository.CompetenceRepository;
import ict.kth.se.model.repository.jdbc.JdbcCompetenceProfileRepository.CompetenceProfileRowMapper;

public class JdbcCompetenceProfileRepository implements CompetenceProfileRepository{

	private JdbcTemplate jdbcTemplate;
	private RowMapper<CompetenceProfile> rowMapper = new CompetenceProfileRowMapper();
	private CompetenceRepository competenceRepository;
	
	/**
	 * public constructor, sets jdbctemplate, competencerepository
	 * @param dataSource datasource to set jdbctemplate
	 * @param competenceRepository competencerepository to be set
	 */
	public JdbcCompetenceProfileRepository(DataSource dataSource, CompetenceRepository competenceRepository) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.competenceRepository = competenceRepository;
	}
	
	/**
	 * Get CompetenceProfile by id
	 * @param id Id of competenceProfile
	 * @return Found competenceProfile
	 */
	@Override
	public CompetenceProfile getCompetenceProfileById(Long id) throws SQLException{
		try{
			String sql = "SELECT * FROM Competence_profile WHERE competence_profile_id = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, id);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get CompetenceProfiles by person's id
	 * @param id Id of person to get competenceProfile
	 * @return Found competenceProfiles
	 */
	@Override
	public List<CompetenceProfile> getCompetenceProfilesByPersonId(Long personId) throws SQLException{
		try{
			String sql = "SELECT * FROM Competence_profile WHERE person_id = ?;";
			return jdbcTemplate.query(sql, rowMapper, personId);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get all competenceProfile
	 * @return Found competenceProfiles
	 */
	@Override
	public List<CompetenceProfile> getAllCompetenceProfiles() throws SQLException{
		try{
			String sql = "SELECT * FROM Competence_profile;";
			return jdbcTemplate.query(sql, rowMapper);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method removes one competenceProfile from database (by it's ID)
	 * @param competenceProfile The competenceProfile to be removed
	 * @throws EntityDoesntExistInDBException If competenceProfile doesn't exist in database
	 * @throws SQLException
	 */
	@Override
	public void removeCompetenceProfile(CompetenceProfile competenceProfile) throws EntityDoesntExistInDBException, SQLException{
		if(this.getCompetenceProfileById(competenceProfile.getId()) == null)
			throw new EntityDoesntExistInDBException("No such competence profile in database");
		
		String sql = "DELETE FROM Competence_profile WHERE competence_profile_id = ?;";
		jdbcTemplate.update(sql, competenceProfile.getId());
	}
	
	/**
	 * This method removes one competenceProfile from database by person's ID
	 * @param personId ID of the person to delete competenceProfiles for
	 * @throws EntityDoesntExistInDBException If competenceProfile doesn't exist in database
	 * @throws SQLException
	 */
	@Override
	public void removeCompetenceProfilesForPerson(Long personId) throws EntityDoesntExistInDBException, SQLException{
		String sql = "DELETE FROM Competence_profile WHERE person_id = ?;";
		jdbcTemplate.update(sql, personId);
	}
	
	/**
	 * This method adds an competenceProfile in DB
	 * @param competenceProfile CompetenceProfile to be added
	 * @param personId Id of person that competenceProfile should be connected to
	 * @return Newly created competenceProfile
	 * @throws EntityAlreadyExistsInDBException If such competenceProfile already exists in DB (by name)
	 * @throws SQLException
	 */
	@Override
	public CompetenceProfile createCompetenceProfile(CompetenceProfile competenceProfile, Long personId) throws EntityAlreadyExistsInDBException, SQLException{
		//Some controll before insertion
		if(competenceProfile == null)
			throw new SQLException("CompetenceProfile can't be null.");
		
		if(personId == null)
			throw new SQLException("Person's ID can't be null.");
		
		if(competenceProfile.getCompetence() == null || competenceProfile.getCompetence().getId() == null 
				|| competenceProfile.getYearsOfExperience() == null)
			throw new SQLException("Corrupted input parameters.");
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final Long competence_id = competenceProfile.getCompetence().getId();
		final Long personIdFinal = personId;
		final Float yearsOfExperience = competenceProfile.getYearsOfExperience();
		
		final String sql = "INSERT INTO Competence_profile " +
				"(competence_id, person_id, years_of_experience) VALUES (?, ?, ?);";
		
		jdbcTemplate.update(
			    new PreparedStatementCreator() {
			        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"competence_profile_id"});
			            ps.setLong(1, competence_id);
			            ps.setLong(2, personIdFinal);
			            ps.setFloat(3, yearsOfExperience);
			            return ps;
			        }
			    },
			    keyHolder);
		
		//After update add new id and return
		competenceProfile.setId(keyHolder.getKey().longValue());
		return competenceProfile;
		
	}
	
	/**
	 * This method updates an competenceProfile in DB
	 * @param competenceProfile CompetenceProfile to be updated
	 * @param personId Id of person that competenceProfile should be connected to
	 * @throws EntityDoesntExistInDBException If such competenceProfile doesn't exist in DB (by id)
	 * @throws SQLException
	 */
	@Override
	public void updateCompetenceProfile(CompetenceProfile competenceProfile, Long personId) 
			throws EntityDoesntExistInDBException, SQLException{
		
		//Some controll before updating
		if(competenceProfile == null)
			throw new SQLException("Competence profile can't be null.");
		
		if(personId == null)
			throw new SQLException("Person's ID can't be null.");
		
		if(this.getCompetenceProfileById(competenceProfile.getId()) == null)
			throw new EntityDoesntExistInDBException("No such competence profile in database");
		
		if(competenceProfile.getCompetence() == null || competenceProfile.getCompetence().getId() == null 
				|| competenceProfile.getYearsOfExperience() == null)
			throw new SQLException("Corrupted input parameters.");
		
		String sql = "UPDATE Competence_profile SET competence_id = ?, person_id = ?, years_of_experience = ? " +
				"WHERE competence_profile_id = ?;";
		
		jdbcTemplate.update(sql, competenceProfile.getCompetence().getId(), personId,
				competenceProfile.getYearsOfExperience(), competenceProfile.getId());
	}
	
	/**
	 * RowMapper to map competenceProfile objects 
	 */
	class CompetenceProfileRowMapper implements RowMapper<CompetenceProfile>{
		public CompetenceProfile mapRow(ResultSet rs, int rowNum) throws SQLException{
			return mapCompetenceProfile(rs);
		}
	}
	
	/**
	 * Maps a row returned from a query of CompetenceProfile to a CompetenceProfile object.
	 * @param rs the result set with its cursor positioned at the current row
	 */
	private CompetenceProfile mapCompetenceProfile(ResultSet rs) throws SQLException {
		CompetenceProfile competenceProfile = new CompetenceProfile();
		competenceProfile.setId(rs.getLong("competence_profile_id"));
		competenceProfile.setYearsOfExperience(rs.getFloat("years_of_experience"));
		
		competenceProfile.setCompetence(competenceRepository.getCompetenceById(rs.getLong("competence_id")));
		return competenceProfile;
	}
}
